import pymysql
import pymysql.cursors
import datetime
# 创建数据库连接
def create_database_connection(host_name, user_name, user_password, database_name,port):
    '''
    host_name:主机
    user_name:用户名
    user_password:密码
    database_name:密码
    port:端口名
    '''
    connection = None
    try:
        connection = pymysql.connect(
            host=host_name,
            user=user_name,
            password=user_password,
            database=database_name,
            charset='utf8mb4',
            port=port,
            cursorclass=pymysql.cursors.DictCursor
        )
        print(f"{datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')}  Database connection successful.")
    except pymysql.MySQLError as e:
        print(f"{datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')}  Error while connecting to MySQL: {e}")
    return connection
# 数据库查询
def sql_select(connection, sql_query):
    '''
    connection:连接对象
    sql_query:查询sql语句
    '''
    try:
        with connection.cursor() as cursor:
            cursor.execute(sql_query)
            results = cursor.fetchall()
            return results
    except pymysql.MySQLError as error:
        return f"{datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')}  Error: {error}"

# 插入数据库
def sql_insert(connection, table_name, data_list):
    '''
    connection:连接对象
    table_name:表名
    data_list:数据列表
    '''
    if not connection:
        print(f"{datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')}  数据库未连接！！！")
        return
    try:
        # Get the columns from the first item in the data list
        if not data_list:
            print(f"{datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')}  数据列表data_list为空！！！")
            return
        columns = data_list[0].keys()
        # Construct the SQL query
        sql = f"INSERT INTO `{table_name}` ({', '.join([f'`{col}`' for col in columns])}) VALUES ({', '.join(['%s'] * len(columns))})"
        # Flatten the data list into a list of tuples
        values = [tuple(item.values()) for item in data_list]
        with connection.cursor() as cursor:
            # Execute the query with all the values
            cursor.executemany(sql, values)
            # Commit the transaction
            connection.commit()
        print(f"{datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')}  成功新增 {len(values)} 条记录到 {table_name}.")
    except pymysql.MySQLError as error:
        print(f"{datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')}  Error: {error}")
        # Rollback the transaction in case of error
        connection.rollback()
